-- =============================================
-- This stored procedure cares a unique section name (Nav_Menu.Name field)
-- for a specified Nav_Menu record.
--
-- Modifications
-- 08/22/2003   E.Tatsui
-- =============================================

CREATE      PROCEDURE amsp_CMGetUniqueSectionName
    @InNavMenuID numeric = NULL,
  @InTitle varchar(255) = NULL,
  @OutSectionName varchar(255) OUTPUT
AS
BEGIN

  IF @InNavMenuID IS NULL AND @InTitle IS NULL BEGIN
    RAISERROR('Either NavMenuID or Title is required as input parameter',16,1)
    RETURN
  END

  DECLARE
    @Title varchar(255),
    @Name varchar(255),
    @RowNum integer,
    @Counter integer

  IF @InTitle IS NOT NULL
    SET @Title = @InTitle
  ELSE
    SELECT @Title = Title
      FROM Nav_Menu
     WHERE NavMenuID = @InNavMenuID

  -- Remove special characters that shouldn't be used in URL.
  SET @Name = REPLACE(@Title, ' ', '_')
  SET @Name = REPLACE(@Name, '.', '_')
  SET @Name = REPLACE(@Name, '?', '_')
  SET @Name = REPLACE(@Name, '\', '_')
  SET @Name = REPLACE(@Name, '/', '_')
  SET @Name = REPLACE(@Name, '*', '_')
  SET @Name = REPLACE(@Name, ':', '_')
  SET @Name = REPLACE(@Name, '|', '_')
  SET @Name = REPLACE(@Name, '<', '_')
  SET @Name = REPLACE(@Name, '&', '_')
  SET @Name = REPLACE(@Name, '''', '_')
  SET @Name = REPLACE(@Name, '#', '_')
  SET @Name = REPLACE(@Name, '>', '_')
  SET @Name = REPLACE(@Name, '>', '_')
  SET @Name = REPLACE(@Name, '>', '_')

  -- Multiple underscores looks so untidy.
  WHILE CHARINDEX('__', @Name) > 0 BEGIN
    SET @Name = REPLACE(@Name, '__','_')
  END
  
  SET @Name = Left(@Name,100)

  -- It needs to be unique.
  SELECT @RowNum = COUNT(*)
    FROM Nav_Menu
   WHERE UPPER(Name) = UPPER(@Name)

  -- If we find a record with the same directory name, let's append a number at the end.    
  SET @Counter = 0
  WHILE @RowNum > 0 BEGIN
    SET @Counter = @Counter + 1
    SELECT @RowNum = COUNT(*)
      FROM Nav_Menu
     WHERE UPPER(Name) = UPPER(@Name) + Cast(@Counter as varchar(255))
  END    
  
  IF @Counter > 0
    SET @Name = @Name + Cast(@Counter as varchar(255))

  SET @OutSectionName = @Name
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetUniqueSectionName] TO [IMIS]
GO